﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmSalesReport

    Sub Clear()

    End Sub


    Sub FillSalesUsers()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select distinct SavedUser From ProspectCases Where Branch=N'" & Branch & "'", cnn)
            Dim Reader As SqlDataReader
            Dim X As Integer = 1

            Clear()
            Me.ListPendingTrans.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("SavedUser")
                ' Item.SubItems.Add(Reader.Item("SavedUser"))

                Me.ListPendingTrans.Items.Add(Item)
                X += 1
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        If Me.RadProspects.Checked = True Then
            If Me.ListPendingTrans.SelectedItems.Count = 0 Then
                MsgBox("Please select a user from the list")
                Exit Sub
            Else
                ShowProspects()
            End If
        End If
        If Me.RadProspectsCases.Checked = True Then
            ShowProspectsCases()
        End If
        If Me.RadPhone.Checked = True OrElse Me.RadVisit.Checked = True Then
            If Me.ListPendingTrans.SelectedItems.Count = 0 Then
                MsgBox("Please select a user from the list")
                Exit Sub
            Else
                ShowSalesReports()
            End If
        End If
    End Sub

    Private Sub frmSalesReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillSalesUsers()
    End Sub

    Sub ShowSalesReports()
        Try
            Me.Cursor = Cursors.Default

            Dim TypeCond As String
            If Me.RadPhone.Checked = True Then
                TypeCond += "and ProspectsNotes.Type='Phone'"
            ElseIf Me.RadVisit.Checked = True Then
                TypeCond = "and ProspectsNotes.Type='Visit'"
            End If

            Dim dap As New SqlDataAdapter("Select ProspectCases.*,ProspectsNotes.* FROM ProspectCases LEFT JOIN ProspectsNotes ON " & _
                                      " ProspectCases.SNo =  ProspectsNotes.ProsNo where ProspectCases.SavedUser=N'" & Me.ListPendingTrans.SelectedItems(0).Text & "' and " & _
                                      "SavedDate>=N'" & Me.DTPPeriodFrom.Value.ToShortDateString & " 00:00:00' " & _
                                      "and SavedDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:23:59'" & TypeCond, cnn)
            Dim das As New DsSales

            dap.Fill(das, "Sales")

            Dim rpt As New SalesReport
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowProspectsCases()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From ProspectCases", cnn)

            Dim das As New DsSales

            dap.Fill(das, "Sales")

            Dim rpt As New Prospects
            rpt.SetDataSource(das)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowProspects()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Prospects where UserName=N'" & Me.ListPendingTrans.SelectedItems(0).Text & "' and " & _
                                      "TransDate>=N'" & Me.DTPPeriodFrom.Value.ToShortDateString & " 00:00:00' " & _
                                      "and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:23:59'", cnn)

            Dim das As New DsProspects

            dap.Fill(das, "Prospects")

            Dim rpt As New rptDailyProspects
            rpt.SetDataSource(das)


            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RadProspects_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadProspectsCases.CheckedChanged
        If RadProspectsCases.Checked = True Then
            Me.ListPendingTrans.Enabled = False
            Me.DTPPeriodFrom.Enabled = False
            Me.DTPPeriodTo.Enabled = False
            Me.ListPendingTrans.Items.Clear()
        ElseIf RadProspectsCases.Checked = False Then
            Me.ListPendingTrans.Enabled = True
            Me.DTPPeriodFrom.Enabled = True
            Me.DTPPeriodTo.Enabled = True
        End If
    End Sub
End Class